package homelesspartners.server.utils;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Enumeration;
import java.util.Hashtable;

import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import ashbyutils.database.testing.JndiUnitTestHelper;

public class SortOrderUpdater {

	private static final String FILE_NAME = "src/homelesspartners/server/DatabaseUnitTests.properties";
	private static final int[][] SORT_ORDERS = { {}, { 7 }, { 8, 4 }, { 9, 5, 2 }, { 10, 6, 3, 1 } };

	/**
	 * @param args
	 * @throws NamingException
	 * @throws IOException
	 * @throws SQLException
	 */
	public static void main(String[] args) throws IOException, NamingException, SQLException {

		// set all stories sort orders to 0
		// get gift request count for all stories
		// assume there are no pledges for any and set sort orders accordingly
		// get pledge counts for stories that have pledges
		// set those stories again
		// loop through all and do the update
		
		String fileName = null;
		if (args.length == 0) {
			fileName = FILE_NAME;
		} else {
			fileName = args[0];
		}
		JndiUnitTestHelper.init(fileName);
		InitialContext context = new InitialContext();
		DataSource dataSource = (DataSource) context.lookup("java:/comp/env/jdbc/homelesspartners");
		Connection connection = dataSource.getConnection();

		String resetAllSQL = "update HOMELESSPARTNERS.STORIES set SORT_ORDER=0";
		String giftRequestCountSQL =
				"select S.ID, count(G.ID) as NUM from HOMELESSPARTNERS.STORIES S, HOMELESSPARTNERS.GIFTS G where S.ID=G.FK_STORIES_ID group by S.ID";
		String giftPledgeCountSQL =
				"select S.ID, count(G.ID) as NUM from HOMELESSPARTNERS.STORIES S, HOMELESSPARTNERS.GIFTS G, HOMELESSPARTNERS.PLEDGES P where S.ID=G.FK_STORIES_ID and G.ID=P.FK_GIFT_ID group by S.ID";
		String updateStoryCountSQL = "update HOMELESSPARTNERS.STORIES set SORT_ORDER=? where ID=?";

		PreparedStatement resetAllStatement = connection.prepareStatement(resetAllSQL);
		PreparedStatement giftRequestCountStatement = connection.prepareStatement(giftRequestCountSQL);
		PreparedStatement giftPledgeCountStatement = connection.prepareStatement(giftPledgeCountSQL);
		PreparedStatement updateStoryCountStatement = connection.prepareStatement(updateStoryCountSQL);

		int updated = resetAllStatement.executeUpdate();
		System.out.println("Number of stories reset to 0: " + updated);

		ResultSet results = giftRequestCountStatement.executeQuery();
		Hashtable allStoriesGiftCounts = new Hashtable();
		Hashtable allStoriesPledgeCounts = new Hashtable();

		while (results.next()) {
			int storyId = results.getInt("s.id");
			int giftCount = results.getInt("NUM");
			allStoriesGiftCounts.put(new Integer(storyId), new Integer(giftCount));
		}

		results = giftPledgeCountStatement.executeQuery();
		while (results.next()) {
			int storyId = results.getInt("s.id");
			int pledgeCount = results.getInt("NUM");
			allStoriesPledgeCounts.put(storyId, pledgeCount);
		}

		Enumeration storyIds = allStoriesGiftCounts.keys();
		while (storyIds.hasMoreElements()) {
			int storyId = (Integer) storyIds.nextElement();
			int giftRequestCount = (Integer) allStoriesGiftCounts.get(storyId);
			int pledgeCount = 0;
			try {
				pledgeCount = (Integer) allStoriesPledgeCounts.get(storyId);
			} catch (NullPointerException e){
				//System.out.println("no pledges for story: " + storyId);
			}

			int sortOrder = 0;
			
			if (pledgeCount >= giftRequestCount) {
				sortOrder = 0; // all requests filled, put at end
			} else {
				// sort according to predetermined order based on number of requests and pledges
				try {
					sortOrder = SORT_ORDERS[giftRequestCount][pledgeCount];
				} catch (Exception e) {
					System.out.println("Could not compute sort order for story: " + storyId + ", " + giftRequestCount + ", " + pledgeCount);
				}
			}
		
			//System.out.println(storyId + ", " + giftRequestCount + ", " + pledgeCount + ", " + sortOrder);
			updateStoryCountStatement.setInt(1, sortOrder);
			updateStoryCountStatement.setInt(2, storyId);

			try {
				int rows = updateStoryCountStatement.executeUpdate();
				if (rows != 1) {
					System.out.println(rows);
					throw new Exception();
				}
			} catch (Exception e) {
				System.err.println("Could not update story: " + storyId);
				System.err.println(e.getMessage());
			}
			}
		
		System.out.println("Done sorting all the gifts");

	}

}
